﻿USE [aspnetdb]
GO

/****** Object:  StoredProcedure [dbo].[mybet_disjoinContest]    Script Date: 03/01/2011 22:39:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Franck Allion
-- Create date: 26/02/11
-- Description:	User disjoin a contest
-- =============================================
CREATE PROCEDURE [dbo].[mybet_disjoinContest]
	@UserId             uniqueidentifier,
	@CustomContestId    int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    DECLARE @SubscriptionId int
    SELECT @SubscriptionId = 0
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    ELSE
		BEGIN
			SET @ErrorCode = -1
    		GOTO Cleanup
		END
		
		
    SELECT  @SubscriptionId = SubscriptionId FROM Subscriptions 
		WHERE UserId=@UserId AND CustomContestId=@CustomContestId
    
    IF ( @SubscriptionId <> 0 )
    BEGIN
 	    -- Insert new subscription
		DELETE Ranks WHERE SubscriptionId=@SubscriptionId    		
 		DELETE Subscriptions WHERE SubscriptionId=@SubscriptionId
    END
    		
	DELETE UserBets WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -3
		GOTO Cleanup
    END
	
	-- Delete user role
	DELETE aspnet_UsersInRoles 
		WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -4
		GOTO Cleanup
    END		
    
	-- Give player role
	-- Get Player role id
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE RoleName = 'Registered Users'
    IF ( @RoleId IS NULL )
    BEGIN
 		SET @ErrorCode = -5
		GOTO Cleanup       
    END
	
	-- Add role	
	INSERT INTO aspnet_UsersInRoles  
	( UserId, 
	  RoleId )
	VALUES
	( @UserId, @RoleId)
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -6
		GOTO Cleanup
    END
    
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END


GO
